﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class Questao : IDisposable
    {
        #region " Listar "

        public DataView ListarQuestaoPorProcesso(int cod_processo_pro)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarQuestaoPorProcesso", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_processo_pro", cod_processo_pro);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_questao");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Consultar "

        public DataView ConsultarQuestao(int cod_questao_que)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarQuestao", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_questao_que", cod_questao_que);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_questao");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public void IncluirQuestao(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();                

                foreach (DataRow lRow in dsRegistro.Tables[0].Rows)
                {
                    SqlCommand sqlc = new SqlCommand("IncluirQuestao", sql);
                    sqlc.CommandType = CommandType.StoredProcedure;
                    sqlc.Parameters.AddWithValue("@num_questao_que", lRow["num_questao_que"]);
                    sqlc.Parameters.AddWithValue("@des_letra_que", lRow["des_letra_que"]);
                    sqlc.Parameters.AddWithValue("@cod_processo_pro", lRow["cod_processo_pro"]);
                    sqlc.Parameters.AddWithValue("@tip_questao_que", lRow["tip_questao_que"]);                    
                    sqlc.ExecuteNonQuery();
                }

                sql.Close();
            }
        }

        #endregion

        #region " Excluir "

        public void ExcluirQuestaoPorProcesso(int cod_processo_pro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirQuestaoPorProcesso", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_processo_pro", cod_processo_pro);
                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
